from sqlalchemy.orm import Session, joinedload
from schema import *
from models import *

"""
   学生就业管理数据库操作
"""

# 查询所有学生的就业情况
def show_employment_info(db: Session):
    return db.query(Stu_employment_db).options(joinedload(Stu_employment_db.to_student)).join(Student_db).all()

# 按照学生编号，公司名字，工资范围查询学生就业信息
def get_employment_info_by(stu_id:int,cp_name:str,sal_min:int,sal_max,db: Session,):
    query = db.query(Stu_employment_db)
    # 动态添加查询条件
    if stu_id is not None:
        query = query.filter(Stu_employment_db.student_id == stu_id)

    if cp_name is not None:
        query = query.filter(Stu_employment_db.company == cp_name)

    if sal_min is not None:
        query = query.filter(Stu_employment_db.salary >= sal_min)

    if sal_max is not None:
        query = query.filter(Stu_employment_db.salary <= sal_max)

    # 执行查询
    results = query.all()

    # 如果没有任何查询条件，返回提示信息
    if not any([stu_id, cp_name, sal_min is not None, sal_max is not None]):
        return "请输入查询内容!"

    return results if results else "未找到匹配的就业信息"

# 更新学生就业信息
def update_stu_employment(db:Session,stu_emp:Stu_employment):
    student_exist = db.query(Stu_employment_db).filter(Stu_employment_db.student_id == stu_emp.student_id).first()
    if student_exist:
        db.query(Stu_employment_db).filter(Stu_employment_db.student_id == stu_emp.student_id).update(stu_emp.dict())
        db.commit()
        return f"{stu_emp.student_id}就业信息更新成功!"
    else:
        return f"{stu_emp.student_id}就业信息不存在!"

# 删除学生就业信息
def delete_stu_employment(db:Session,stu_id:int):
    stu_exist = db.query(Stu_employment_db).filter(Stu_employment_db.student_id==stu_id).first()
    if stu_exist:
        db.query(Stu_employment_db).filter(Stu_employment_db.student_id==stu_id).delete()
        db.commit()
        return f"{stu_id}删除成功!"
    else:
        f"{stu_id}不存在!"

# 添加学生就业信息
def add_stu_employment(db:Session,stu_emp:Stu_employment):
    stu_exist = db.query(Stu_employment_db).filter(Stu_employment_db.student_id == stu_emp.student_id).first()
    if stu_exist:
        return f"{stu_emp.student_id}已存在，不需要添加!"
    else:
        db.add(Stu_employment_db(**stu_emp.dict()))
        db.commit()
        return f"{stu_emp.student_id}已经添加成功!"

